package columbia.edu;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import oracle.jdbc.pool.OracleDataSource;
/**
* Servlet implementation class OracleServlet
*/
public class SearchResultServlet extends HttpServlet {
private static final long serialVersionUID = 1L;
private static final String connect_string =
//"jdbc:oracle:thin:scott/tiger@//w4111b.cs.columbia.edu:1521/ADB";
	//"jdbc:oracle:thin:ab3416/kFxJssQk@//w4111b.cs.columbia.edu:1521/ADB";
	"jdbc:oracle:thin:zzn2101/ycmrmMbM@//w4111b.cs.columbia.edu:1521/ADB";
private Connection conn;
/**
* @see HttpServlet#HttpServlet()
*/
public SearchResultServlet() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
response)
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
PrintWriter pw = new PrintWriter(response.getOutputStream());
try {
conn = (Connection) request.getSession().getAttribute("Connection");
if (conn == null) {
// Create a OracleDataSource instance and set URL
OracleDataSource ods = new OracleDataSource();
ods.setURL(connect_string);
conn = ods.getConnection();
request.getSession().setAttribute("Connection", conn);
}
Statement stmt = conn.createStatement();
//String custID = (String)(request.getSession().getAttribute("custID"));
//custID=custID.trim();
String searchText = request.getParameter("searchText");
searchText.trim();
if (searchText != "" || searchText != null) {
	/*ResultSet rset = stmt.executeQuery("select p.pid, description, price, type, " +
			"decode(category, 'T','Tops','P','Pants','O','Others') as categoryClothes, brand, author, " +
			"isbn from " +
			"products p, clothes c, books b where p.pid=b.pid and p.pid=c.pid " +
			"and description like '%" + searchText + "%' or brand like '%" + searchText + 
			"%' or decode(category, 'T','Tops','P','Pants','O','Others') like '%" + searchText + "%' " +
					"or author like '%" + searchText + "%' or isbn like '%" + searchText + "%'");*/ 
	
	pw.println("<html>");
	pw.println("<head><title>Search Results</title></head>");
	pw.println("<link rel='stylesheet' href='style.css' type='text/css' charset='utf-8' />");
	pw.println("<BR><BR>");
	pw.println("<H1>The following results matched your search criteria<BR></H1>");
	pw.println("<body>");
	
	
	ResultSet rset = stmt.executeQuery("select distinct p.pid, p.type, description, price, " +
			"decode(category, 'T','Tops','P','Pants','O','Others') as clothesCategory, " + 
			"brand" + 
			" from products p, clothes c " + 
			"where p.pid=c.pid " +
			"and (lower(description) like lower('%" + searchText + "%') or lower(brand) like " +
					"lower('%" + searchText + 
			"%') or lower(decode(category, 'T','Tops','P','Pants','O','Others')) like lower('%" + 
			searchText + "%'))");
	response.setContentType("text/html");
	
	pw.println("<BR>");
	pw.println("<BR>");
	pw.println("<H2>");
	while (rset.next()) {
		char type = rset.getString("type").trim().charAt(0);
		String prod_id = rset.getString("pid").trim();
		String desc = rset.getString("description");
		String price = rset.getString("price");
		request.getSession().setAttribute("ID", prod_id);
		
			pw.print("Type Of Product : ");
			pw.println ("Clothes<BR>");
			pw.print("Category : ");
			pw.println (rset.getString("clothesCategory") + "<BR>");
			pw.print("Brand : ");
			pw.println (rset.getString("brand") + "<BR>");
			pw.print("Description : ");
			pw.println (rset.getString("description") + "<BR>");
			pw.print("Price : ");
			pw.println (rset.getString("price") + "<BR>");
			String attr = "pid";
			//request.setAttribute("pid", prod_id);
			//pw.println("<a href = Details.jsp?prodID=" + prod_id + ">Click here to purchase this product</a><BR>");
			pw.println("<a href = Details.jsp?prodID=" + prod_id + ">Click here to purchase this product</a><BR>");

			pw.println("<BR>");
			pw.println("<BR>");
	}
	rset.close();
	pw.println("</H2>");
	
	ResultSet rset1 = stmt.executeQuery("select distinct p.pid, p.type, description, price, " +
			"isbn, " + 
			"author" + 
			" from products p, books b " + 
			"where p.pid=b.pid " +
			"and (lower(description) like lower('%" + searchText + "%') or " +
					"lower(isbn) like lower('%" + searchText + 
			"%') or lower(author) like lower('%" + searchText + "%'))");
	response.setContentType("text/html");
	
	pw.println("<H2>");		
	while (rset1.next()) {
		String prod_id = rset1.getString("pid").trim();
		request.getSession().setAttribute("ID", prod_id);
			pw.print("Type of Product : ");
			pw.println ("Books<BR>");
			pw.print("Author : ");
			pw.println (rset1.getString("author") + "<BR>");
			pw.print("ISBN : ");
			pw.println (rset1.getString("isbn") + "<BR>");
			pw.print("Description : ");
			pw.println (rset1.getString("description") + "<BR>");
			pw.print("Price : ");
			pw.println (rset1.getString("price") + "<BR>");
			pw.println("<a href = Details.jsp?prodID=" + prod_id + ">Click here to purchase this product</a><BR>");
			//pw.println("<a href = Details.jsp onclick = Details.jsp?prodID='" + prod_id + "'>Click here to purchase this product</a><BR>");
			pw.println("<BR>");
			pw.println("<BR>");
			
	}
	pw.println("</H2>");
	
	pw.println("<div id='nav-top'>");
	pw.println("<ul>");
	pw.println("<li><a href='Logout.jsp'>Logout</a> |</li>");
	//pw.println("<li><a href='Account.jsp'>My Account</a> |</li>");
	pw.println("<li><a href='Contact.jsp'>Contact us</a></li>");
	pw.println("</ul>");
	pw.println("</div>");
	pw.println("</body></html>");	
}
} catch (SQLException e) {
pw.println(e.getMessage());
}
pw.close();
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
response)
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
// TODO Auto-generated method stub
}
}